<?php

namespace app\admin\controller;

use app\common\controller\AdminBase;
use think\App;
use think\cache\driver\Redis;
use util\Excel;

/**
 * Markorder控制器
 */
class Markorder extends AdminBase
{

    protected $model = null;

    protected $validate = null;

    protected $relationSearch = false;

    public function __construct(App $app)
    {
        parent::__construct($app);


        $this->model = new \app\admin\model\Order();

        $this->validate = \app\admin\validate\Order::class;
    }


    /**
     * 列表
     */
    public function index()
    {
        // list($limit, $where, $sortArr) = $this->buildTableParames();
        $limits = $this->request->get('limit', 10);
        $page = $this->request->get('page', 1);
        $newpage = $page - 1;
        if ($newpage == 0) {
            $limit1 = 0;
            $limit2 = $limits;
        } else {
            $limit1 = $newpage * $limits;
            $limit2 = $limits;
        }
        $filters = $this->request->get('filter', '{}');
        $filters = json_decode($filters, true);
        $where2 = array();
        if (!empty($filters['email'])) {
            $where2[] = array('a.email', 'LIKE', '%' . $filters['email'] . '%');
        }
        if (!empty($filters['tid'])) {
            $where2[] = array('d.tid', 'LIKE', '%' . $filters['tid'] . '%');
        }
        if (!empty($filters['name'])) {
            $where2[] = array('a.name', 'LIKE', '%' . $filters['name'] . '%');
        }
        if (!empty($filters['sku'])) {
            $where2[] = array('c.sku', 'LIKE', '%' . $filters['sku'] . '%');
        }
        if (!empty($filters['tracking_number'])) {
            $where2[] = array('a.tracking_number', 'LIKE', '%' . $filters['tracking_number'] . '%');
        }
        if (!empty($filters['shipping_name'])) {
            $where2[] = array('a.shipping_name', 'LIKE', '%' . $filters['shipping_name'] . '%');
        }
        if (!empty($filters['create_at']) && isset($filters['create_at'][0]) && isset($filters['create_at'][1])) {
            $where2[]  = ['a.created_at', '>=', strtotime($filters['create_at'][0])];
            $where2[]  = ['a.created_at', '<=', strtotime($filters['create_at'][1])];
        }
        // halt($where2);
        //         ➢ 客户姓名
        // ➢ 邮箱
        // ➢ 订单编号
        // ➢ 订单日期
        // ➢ 物流单号-需补充
        // ➢ SKU
        // ➢ 变体信息-需补充
        // ➢ 订单金额---新页面不需显示
        // ➢ 店铺
        // ➢ 问题类型
        if (empty($where2) && $page == 1 && $limits == 10) {
            // $redis = new Redis();
            // $lists = $redis->get('lists_markorder');
            // if (empty($lists)) {
            //     $lists = table('order')
            //         ->alias('a')
            //         ->field('a.*')
            //         ->where($where2)
            //         ->order('a.created_at desc')
            //         ->limit(1)
            //         ->select()->toarray();
            //         // halt($lists);
            //     foreach ($lists as $k => $v) {
            //         $site = table('site')
            //             ->where(['id' => $v['site_id']])->find();
            //         if (!empty($site)) {
            //             $lists[$k]['site_name'] = $site['site_name'];
            //         } else {
            //             $lists[$k]['site_name'] = '未知';
            //         }
            //         $lists[$k]['created_at'] = date('Y-m-d H:i:s', $v['created_at']);
            //     }
            //     $redis->set('lists_markorder', $lists, 30 *24 * 3600);
            // }

            $count = 0;
            $qustiontype = table('qustiontype')->order('sort asc')->select()->toarray();
            $rst = [];
            $lists = [];
            foreach ($qustiontype as $k => $v) {
                $rst[$k] = [
                    'value' => $v['id'],
                    'label' => $v['title'],
                ];
            }
            $qustype = array_merge([['value' => 0, 'label' => '全部']], $rst);

        } else {
            $where3[] = ['a.is_mark', '=', 1];
            $where3[] = ['c.sku', '>', 0];
            $lists = table('order')
                ->alias('a')
                ->field('a.shipping_name,a.email,a.name,a.id,a.created_at,a.tracking_number,c.sku,b.variant_title,a.site_id,a.two_kf,a.is_mark,a.mark_time')
                ->join('qu_order_quantity b', 'a.id = b.order_id', 'left')
                ->join('qu_order_sku c', 'b.variant_id = c.variants_id', 'left')
                ->join('qu_kfdetail d', 'a.id = d.orderid', 'left')
                ->where($where2)
                ->where($where3)
                ->group('a.id')
                ->order('a.created_at desc')
                ->limit($limit1, $limit2)
                ->select()->toarray();
            foreach ($lists as $k => $v) {
                $site = table('site')
                    ->where(['id' => $v['site_id']])->find();
                if (!empty($site)) {
                    $lists[$k]['site_name'] = $site['site_name'];
                } else {
                    $lists[$k]['site_name'] = '未知';
                }
                $lists[$k]['created_at'] = date('Y-m-d H:i:s', $v['created_at']);
                $lists[$k]['mark_time'] = date('Y-m-d H:i:s', $v['mark_time']);
                $lists[$k]['qustiontype'] =  $this->getTypeById($v['id']);
            }
            $qustiontype = table('qustiontype')->order('sort asc')->select()->toarray();
            $rst = [];
            foreach ($qustiontype as $k => $v) {
                $rst[$k] = [
                    'value' => $v['id'],
                    'label' => $v['title'],
                ];
            }
            $qustype = array_merge([['value' => 0, 'label' => '全部']], $rst);
            $qustion = table('qustiontype')->select()->toarray();
            $rst = [];
            //查询qustion详情表
            foreach ($qustion as $v) {
                $qustiondetail = table('qustiondetail')->field('id as value,title as label')->where(['pid' => $v['id']])->select()->toarray();
                $rst[] = [
                    'value' => $v['id'],
                    'label' => $v['title'],
                    'children' => $qustiondetail,
                ];
            }

            $where3[] = ['a.is_mark', '=', 1];
            $where3[] = ['c.sku', '>', 0];
            $count = table('order')
                ->alias('a')
                ->field('a.shipping_name,a.email,a.name,a.id,a.created_at,a.tracking_number,c.sku,b.variant_title,a.site_id,a.two_kf,a.is_mark,a.mark_time')
                ->join('qu_order_quantity b', 'a.id = b.order_id', 'left')
                ->join('qu_order_sku c', 'b.variant_id = c.variants_id', 'left')
                ->join('qu_kfdetail d', 'a.id = d.orderid', 'left')
                ->where($where2)
                ->where($where3)
                ->group('a.id')
                ->count();

        }
        
        $data = [
            'code' => 1,
            'msg' => '',
            'count' => (int)$count,
            'qustype' => $qustype,
            'data' => $lists,
            'options' => $rst
        ];

        return json($data);
    }

    //通过订单id查询问题类型

    public function  getTypeById($orderid = 5521485005084)
    {
        //通过订单号查询所有的问题类型
        $qustype = table('kfdetail')->field('tid')->where(['orderid' => $orderid])->group('tid')->select()->toarray();
        $qustype = array_column($qustype, 'tid');
        //查询问题表中的id
        $where[] = ['id', 'in', $qustype];
        $qustiontype = table('qustiontype')->field('title')->where($where)->order('sort', 'asc')->select()->toarray();
        $qustiontype = array_column($qustiontype, 'title');
        $count = count($qustiontype);
        if ($count == 1) {
            return $qustiontype[0];
        }
        $str = implode(',', $qustiontype);
        return $str;
    }


    /**
     * 查找
     */
    public function find()
    {
        $id = input('id');
        $row = $this->model->find($id);
        if (empty($row)) {
            return error('数据不存在');
        }
        $qustiontype = table('qustiontype')->select()->toarray();
        $qustiontype = array_column($qustiontype, 'title', 'id');
        $qustiondetail = table('qustiondetail')->select()->toarray();
        $qustiondetail = array_column($qustiondetail, 'title', 'id');
        $rst = [];
        $kfdetail = table('kfdetail')->field('tid,did')->where(['orderid' => $id])->order('id asc')->select()->toarray();
        foreach ($kfdetail as $key => $value) {
            $rst[$key] = [
                'type_title' => isset($qustiontype[$value['tid']]) ? $qustiontype[$value['tid']] : '',
                'typedetail_title' => isset($qustiondetail[$value['did']]) ? $qustiondetail[$value['did']] : '',
            ];
        }
        $info['kfqustion'] = $rst;
        $info['email_detail'] = $row['email_detail'];
        return success('ok', $info);
    }


    //统计问题分类

    public function  count_qusnum()
    {
        $post = $this->request->post();
        if (!empty($post['qids']) && isset($post['qids'][0]) && isset($post['qids'][1])) {
            $where2[] = ['d.tid', '=', $post['qids'][0]];
            $where2[] = ['d.did', '=', $post['qids'][1]];
        }
        if (!empty($post['tid'])) {
            $where2[] = array('d.tid', '=', $post['tid']);
        }
        if (!empty($post['sku'])) {
            $where2[] = array('c.sku', 'LIKE', '%' . $post['sku'] . '%');
        }
        if (!empty($post['created_at']) && isset($post['created_at'][0]) && isset($post['created_at'][1])) {
            $where2[]  = ['a.created_at', '>=', strtotime($post['created_at'][0])];
            $where2[]  = ['a.created_at', '<=', strtotime($post['created_at'][1])];
        }
        // 产品编号  sku尺码  问题类型   已反馈订单数
        $where2[] = ['a.is_mark', '=', 1];
        // $where2[] = ['e.id', '=', 1];
        $list = table('order')
            ->where($where2)
            ->where('c.sku', 'is not null')
            ->alias('a')
            ->field('COUNT(a.id) as count,c.sku,e.title as ttitle,f.title as dtitle,a.id')
            ->join('qu_order_quantity b', 'a.id = b.order_id', 'left')
            ->join('qu_order_sku c', 'b.variant_id = c.variants_id', 'left')
            ->join('qu_kfdetail d', 'd.orderid = a.id', 'left')
            ->join('qu_qustiontype e', 'd.tid = e.id', 'left')
            ->join('qu_qustiondetail f', 'd.did = f.id', 'left')
            ->order('a.created_at desc')
            ->group('a.id,e.id,f.id,c.sku')
            ->select()
            ->toarray();
        foreach ($list as $k => $v) {
            if (count(explode('-', $v['sku'])) >= 3) {
                $sku = explode('-', $v['sku']);
                $sku = [$sku[0], $sku[2]];
                $sku = implode('-', $sku);
                $list[$k]['sku'] = $sku;
            }
        }
        table('middlecount')->insertAll($list);
        $count_rst['list'] = table('middlecount')->field('SUM(count) as count,sku,ttitle,dtitle,id')->order('count desc')->group('sku,ttitle,dtitle')->select()->toarray();
        $count_rst['count'] = table('middlecount')->sum('count');
        $del_where[] = ['id', '>', 0];
        table('middlecount')->where($del_where)->delete();
        return success('ok', $count_rst);
    }

    /**
     *导出
     */
    public function export()
    {
        // list($limit, $where, $sortArr) = $this->buildTableParames();
        $tableName = $this->model->getName();
        $limits = $this->request->get('limit', 10);
        $page = $this->request->get('page', 1);
        $newpage = $page - 1;
        if ($newpage == 0) {
            $limit1 = 0;
            $limit2 = $limits;
        } else {
            $limit1 = $newpage * $limits;
            $limit2 = $limits;
        }
        $filters = $this->request->get('filter', '{}');
        $filters = json_decode($filters, true);
        $where2 = array();
        if (!empty($filters['email'])) {
            $where2[] = array('a.email', 'LIKE', '%' . $filters['email'] . '%');
        }
        if (!empty($filters['tid'])) {
            $where2[] = array('d.tid', 'LIKE', '%' . $filters['tid'] . '%');
        }
        if (!empty($filters['email'])) {
            $where2[] = array('a.email', 'LIKE', '%' . $filters['email'] . '%');
        }
        if (!empty($filters['name'])) {
            $where2[] = array('a.name', 'LIKE', '%' . $filters['name'] . '%');
        }
        if (!empty($filters['sku'])) {
            $where2[] = array('c.sku', 'LIKE', '%' . $filters['sku'] . '%');
        }
        if (!empty($filters['tracking_number'])) {
            $where2[] = array('a.tracking_number', 'LIKE', '%' . $filters['tracking_number'] . '%');
        }
        if (!empty($filters['shipping_name'])) {
            $where2[] = array('a.shipping_name', 'LIKE', '%' . $filters['shipping_name'] . '%');
        }
        if (!empty($filters['create_at']) && isset($filters['create_at'][0]) && isset($filters['create_at'][1])) {
            $where2[]  = ['a.created_at', '>=', strtotime($filters['create_at'][0])];
            $where2[]  = ['a.created_at', '<=', strtotime($filters['create_at'][1])];
        }
        $qustiontype = table('qustiontype')->select()->toarray();
        $qustiontype = array_column($qustiontype, 'title', 'id');
        $qustiondetail = table('qustiondetail')->select()->toarray();
        $qustiondetail = array_column($qustiondetail, 'title', 'id');
        // halt($where2);
        //         ➢ 客户姓名
        // ➢ 邮箱
        // ➢ 订单编号
        // ➢ 订单日期
        // ➢ 物流单号-需补充
        // ➢ SKU
        // ➢ 变体信息-需补充
        // ➢ 订单金额---新页面不需显示
        // ➢ 店铺
        // ➢ 问题类型
        if (empty($where2) && $page == 1 && $limits == 10) {
            $redis = new Redis();
            $lists = $redis->get('lists_markorder');
            if (empty($lists)) {
                $where3[] = ['a.is_mark', '=', 1];
                $where3[] = ['c.sku', '>', 0];
                $lists = table('order')
                    ->alias('a')
                    ->field('a.shipping_name,a.email,a.name,a.id,a.created_at,a.tracking_number,c.sku,b.variant_title,a.site_id,a.two_kf,a.is_mark,a.mark_time,a.email_detail')
                    ->join('qu_order_quantity b', 'a.id = b.order_id', 'left')
                    ->join('qu_order_sku c', 'b.variant_id = c.variants_id', 'left')
                    ->join('qu_kfdetail d', 'a.id = d.orderid', 'left')
                    ->where($where2)
                    ->where($where3)
                    ->group('a.id')
                    ->order('a.created_at desc')
                    ->limit($limit1, $limit2)
                    ->select()->toarray();
                foreach ($lists as $k => $v) {
                    $site = table('site')
                        ->where(['id' => $v['site_id']])->find();
                    if (!empty($site)) {
                        $lists[$k]['site_name'] = $site['site_name'];
                    } else {
                        $lists[$k]['site_name'] = '未知';
                    }
                    $kfdetail = table('kfdetail')->field('tid,did')->where(['orderid' => $v['id']])->order('id asc')->select()->toarray();
                    $rst = [];
                    foreach ($kfdetail as $key => $value) {
                        $rst[$key] = [
                            'type_title' => isset($qustiontype[$value['tid']]) ? $qustiontype[$value['tid']] : '',
                            'typedetail_title' => isset($qustiondetail[$value['did']]) ? $qustiondetail[$value['did']] : '',
                        ];
                    }
                    $lists[$k]['json_detail'] =  json_encode($rst);
                    $lists[$k]['created_at'] = date('Y-m-d H:i:s', $v['created_at']);
                    $lists[$k]['mark_time'] = date('Y-m-d H:i:s', $v['mark_time']);
                    $lists[$k]['qustiontype'] =  $this->getTypeById($v['id']);
                }
                $redis->set('lists_markorder', $lists, 60 * 5);
            }
        } else {
            $where3[] = ['a.is_mark', '=', 1];
            $where3[] = ['c.sku', '>', 0];
            $lists = table('order')
                ->alias('a')
                ->field('a.shipping_name,a.email,a.name,a.id,a.created_at,a.tracking_number,c.sku,b.variant_title,a.site_id,a.two_kf,a.is_mark,a.mark_time,a.email_detail')
                ->join('qu_order_quantity b', 'a.id = b.order_id', 'left')
                ->join('qu_order_sku c', 'b.variant_id = c.variants_id', 'left')
                ->join('qu_kfdetail d', 'a.id = d.orderid', 'left')
                ->where($where2)
                ->where($where3)
                ->group('a.id')
                ->order('a.created_at desc')
                ->limit($limit1, $limit2)
                ->select()->toarray();
            foreach ($lists as $k => $v) {
                $site = table('site')
                    ->where(['id' => $v['site_id']])->find();
                if (!empty($site)) {
                    $lists[$k]['site_name'] = $site['site_name'];
                } else {
                    $lists[$k]['site_name'] = '未知';
                }
                $kfdetail = table('kfdetail')->field('tid,did')->where(['orderid' => $v['id']])->order('id asc')->select()->toarray();
                $rst = [];
                foreach ($kfdetail as $key => $value) {
                    $rst[$key] = [
                        'type_title' => isset($qustiontype[$value['tid']]) ? $qustiontype[$value['tid']] : '',
                        'typedetail_title' => isset($qustiondetail[$value['did']]) ? $qustiondetail[$value['did']] : '',
                    ];
                }
                $lists[$k]['json_detail'] =  json_encode($rst, JSON_UNESCAPED_UNICODE);
                $lists[$k]['created_at'] = date('Y-m-d H:i:s', $v['created_at']);
                $lists[$k]['mark_time'] = date('Y-m-d H:i:s', $v['mark_time']);
                $lists[$k]['qustiontype'] =  $this->getTypeById($v['id']);
            }
        }
        // 0 => array:14 [
        //     "shipping_name" => "Sylvia Louise Keay"
        //     "email" => "sseddy@live.com"
        //     "name" => "#10800"
        //     "id" => "5648972939548"
        //     "created_at" => "2023-12-26 06:33:40"
        //     "tracking_number" => "4PX3000900209401CN"
        //     "sku" => "bra01-beige+black+pink-6XL"
        //     "variant_title" => "42 / D / 3 PCS Beige+Black+Pink"
        //     "site_id" => 103
        //     "two_kf" => ""
        //     "is_mark" => 1
        //     "mark_time" => "2024-01-15 15:08:58"
        //     "site_name" => "Elizabeth Bra"
        //     "qustiontype" => "尺码问题"
        //   ]
        $header = array();
        $header[] = ['邮箱', 'email'];
        $header[] = ['客户姓名', 'shipping_name'];
        $header[] = ['物流单号', 'tracking_number'];
        $header[] = ['订单编号', 'name'];
        $header[] = ['sku', 'sku'];
        $header[] = ['变体信息', 'variant_title'];
        $header[] = ['问题详情', 'json_detail'];
        $header[] = ['订单时间(美国)', 'created_at'];
        $header[] = ['标记时间', 'mark_time'];
        $header[] = ['邮件详情', 'email_detail'];
        // $header[] = ['店铺', 'site_name'];
        $fileName = "export_" . $tableName . "_" . time();
        return Excel::exportData($lists, $header, $fileName, 'xlsx');
    }
}
